SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TWSACA_REPO_Q80]
/*-------------------------------------------------------*/
/*---Empresa              : OFISIS S.A.                --*/
/*---Cliente              : OFISIS S.A.	               --*/
/*---Sistema              : OFINET - Finanzas          --*/
/*---M+dulo               : Finanzas                   --*/
/*---Programa             : CALCULA SALDO INICIAL      --*/
/*---Script               : SP_TWSACA_REPO_Q80.SQL     --*/
/*---Nombre SP            : SP_TWSACA_REPO_Q80	       --*/
/*---Desarrollado por     : Rosa Contreras             --*/
/*---Fecha Creacion	  : 19/06/2001		       --*/ 
/*---Base Datos           : SQL SERVER		       --*/
/*---Versi+n              : 7.3                        --*/
/*---Invoca a SP          :                            --*/
/*-------------------------------------------------------*/
/*-------------------------------------------------------*/
/*---Comentarios                                       --*/
/*---                                                  --*/
/*---                                                  --*/
/*-------------------------------------------------------*/

/*--------------------------------------------------------------------- */
/* Drop Proc SP_TWSACA_REPO_Q80			 	 		*/
/* Grant execute On SP_TWSACA_REPO_Q80 To Public	 			*/
/* SP_TWSACA_REPO_Q80'10', '001' , null 	   			*/
/*----------------------------------------------------------------------*/
@IDFE_OPER TD_DT_001
	As
DECLARE
@CSCO_EMPR	TD_VC_002,
@CSCO_CAJA	TD_VC_008,
@VSAA_MINI TD_VC_004,
@VSMM_MINI TD_VC_002,


/*Variables*/
@VNIM_INGR     	NUMERIC(16,4),
@VNIM_EGRE	NUMERIC(16,4),
@VNIM_SALD_INIC	NUMERIC(16,4),
@VNIM_SALD_MINI	NUMERIC(16,4)


DECLARE CU_TWSACA_REPO_Q80_1 CURSOR FOR 
SELECT CO_EMPR, CO_CAJA
FROM TMCAJA

BEGIN
DELETE FROM #TWSACA_REPO_AUXI
DELETE FROM #TWSACA_REPO

-- PARA CALCULAR EL SALDO INICIAL DEL PRIMER PERIODO DE LAS CAJAS
Open CU_TWSACA_REPO_Q80_1
FETCH CU_TWSACA_REPO_Q80_1 INTO @CSCO_EMPR , @CSCO_CAJA
WHILE (@@FETCH_STATUS = 0)
BEGIN
	
/*	IF (@@FETCH_STATUS < 0)
	    Begin
		RAISERROR 20001 "FIN DE ARCHIVO"
		CLOSE CU_TWSACA_REPO_Q80_1
		DEALLOCATE CU_TWSACA_REPO_Q80_1
		RETURN
	    End*/


SELECT @VSAA_MINI = MIN(AA_CAJA)
FROM TMSACA_MENS
WHERE RTRIM(CO_EMPR) = RTRIM(@CSCO_EMPR)
AND RTRIM(CO_CAJA) = RTRIM(@CSCO_CAJA)


SELECT @VSMM_MINI = MIN(MM_CAJA)
FROM TMSACA_MENS
WHERE RTRIM(CO_EMPR) = RTRIM(@CSCO_EMPR)
AND RTRIM(CO_CAJA) = RTRIM(@CSCO_CAJA)
AND RTRIM(AA_CAJA) = RTRIM(@VSAA_MINI)

IF @VSAA_MINI IS NOT NULL AND @VSMM_MINI IS NOT NULL 
BEGIN 

  SELECT @VNIM_SALD_MINI = IM_SALD_INIC
  FROM TMSACA_MENS
  WHERE RTRIM(CO_EMPR) = RTRIM(@CSCO_EMPR)
  AND RTRIM(CO_CAJA) = RTRIM(@CSCO_CAJA)
  AND RTRIM(AA_CAJA) = RTRIM(@VSAA_MINI)
  AND RTRIM(MM_CAJA) = RTRIM(@VSMM_MINI)

  INSERT INTO #TWSACA_REPO_AUXI( CO_EMPR, CO_CAJA, IM_SALD_INIC)
  VALUES( @CSCO_EMPR, @CSCO_CAJA, @VNIM_SALD_MINI)


END

FETCH CU_TWSACA_REPO_Q80_1 INTO @CSCO_EMPR , @CSCO_CAJA
END

CLOSE CU_TWSACA_REPO_Q80_1
DEALLOCATE CU_TWSACA_REPO_Q80_1


-- CURSOR DE TODAS LAS CUENTAS
DECLARE CU_TWSACA_REPO_Q80_2 CURSOR FOR 
SELECT CO_EMPR , CO_CAJA 
FROM TMCAJA

Open CU_TWSACA_REPO_Q80_2
FETCH CU_TWSACA_REPO_Q80_2 INTO @CSCO_EMPR , @CSCO_CAJA

WHILE (@@FETCH_STATUS = 0)
BEGIN
	
/*	IF (@@FETCH_STATUS < 0)
	    Begin
		RAISERROR 20001 "FIN DE ARCHIVO"
		CLOSE CU_TWSACA_REPO_Q80_2
		DEALLOCATE CU_TWSACA_REPO_Q80_2
		RETURN
	    End
*/

	SELECT @VNIM_INGR = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
	FROM TCMOVI_CAJA
	WHERE TI_SITU = 'ACT'
	AND TI_MOVI = 'I'
	AND CO_EMPR = @CSCO_EMPR
	AND CO_CAJA = @CSCO_CAJA
	AND FE_OPER < @IDFE_OPER

	SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
	FROM TCMOVI_CAJA 
	WHERE TI_SITU = 'ACT'
	AND TI_MOVI = 'E'
	AND CO_EMPR =
 @CSCO_EMPR
	AND CO_CAJA = @CSCO_CAJA
	AND FE_OPER < @IDFE_OPER

	SELECT @VNIM_SALD_INIC = ISNULL(@VNIM_INGR, 0) - ISNULL( @VNIM_EGRE,0)


	INSERT INTO #TWSACA_REPO(CO_EMPR, CO_CAJA, IM_SALD_INIC )
	VALUES( @CSCO_EMPR, @CSCO_CAJA, @VNIM_SALD_INIC )

	FETCH CU_TWSACA_REPO_Q80_2 INTO @CSCO_EMPR , @CSCO_CAJA
END

CLOSE CU_TWSACA_REPO_Q80_2
DEALLOCATE CU_TWSACA_REPO_Q80_2

-- INSERTO CAJAS QUE NO HAN TENIDO MOVIMIENTOS, ES DECIR AUN NO ESTAN GRABADAS EN TABLA #TWSACA_REPO

INSERT INTO #TWSACA_REPO( CO_EMPR, CO_CAJA, IM_SALD_INIC )
SELECT T2.CO_EMPR, T2.CO_CAJA, 0
FROM TMCAJA T2
WHERE NOT EXISTS
(SELECT T1.CO_EMPR FROM #TWSACA_REPO T1
 WHERE RTRIM(T1.CO_EMPR) = RTRIM(T2.CO_EMPR)
 AND RTRIM(T1.CO_CAJA) = RTRIM(T2.CO_CAJA))

-- ACTUALIZO EL SALDO INICIAL MAS EL SALDO INICIAL DEL PRIMER PERIODO QUE SE ABRIO
UPDATE #TWSACA_REPO 
SET #TWSACA_REPO.IM_SALD_INIC =  (SELECT ISNULL(T2.IM_SALD_INIC,0) + ISNULL(#TWSACA_REPO.IM_SALD_INIC,0) 
				  FROM #TWSACA_REPO_AUXI T2
				  WHERE RTRIM(#TWSACA_REPO.CO_EMPR) = RTRIM(T2.CO_EMPR)
				  AND RTRIM(#TWSACA_REPO.CO_CAJA) = RTRIM(T2.CO_CAJA))
WHERE EXISTS( SELECT T3.CO_EMPR
FROM #TWSACA_REPO_AUXI T3
WHERE #TWSACA_REPO.CO_EMPR = T3.CO_EMPR
AND #TWSACA_REPO.CO_CAJA = T3.CO_CAJA)

END -- FIN SP

/************************************FIN**********************************/


GO
